DESC[RIBE]

Purpose

Use this statement to print column information for a given table or view.

Prerequisites

  • You need either the system privilege USE ANY SCHEMA or the object privilege USAGE on the target schema, or the schema must be owned by you or one of your assigned roles.

  • If the object to be described is a table, one of the following conditions must be fulfilled:
    • The current user has one of the following system privileges: SELECT ANY TABLE (or SELECT ANY DICTIONARY in context of system tables, respectively), INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, ALTER ANY TABLE or DROP ANY TABLE.
    • The current user has any object privilege on the table.
    • The table belongs to the current user or one of his roles.
  • If the object to be described is a view, one of the following conditions must be fulfilled:
    • The current user has one of the following system privileges: SELECT ANY TABLE or DROP ANY VIEW.
    • The current user has any object privilege on the view.
    • The view belongs to the current user or one of his roles.

Syntax

describe::=

Describe statement

Usage Notes

  • The SQL_TYPE column displays the datatype. In case of a string type, the used character set will be additionally shown (ASCII or UTF-8).
  • The NULLABLE column indicates whether the column is permitted to contain NULL values.
  • The value of columns DISTRIBUTION_KEY and PARTITION_KEY show whether the column is part of the distribution and partition keys (for additional information, see ALTER TABLE (Distribution/Partitioning)). For Views these values are always NULL.
  • If you specify the option FULL, then the additional column COLUMN_COMMENT displays the column comment (cut to maximum 200 characters), if this was set either implicitly by the CREATE TABLE or CREATE VIEW command or explicitly by the COMMENT statement.
  • DESCRIBE can be abbreviated by DESC (for example, DESC my_table;).

Example

CREATE TABLE t (i DECIMAL COMMENT IS 'id column',
         d DECIMAL(20,5),
         j DATE,
         k VARCHAR(5),
         DISTRIBUTE BY i,
         PARTITION BY d);
         
DESCRIBE t;

Example Result

COLUMN_NAME SQL_TYPE NULLABLE DISTRIBUTION_KEY PARTITION_KEY
I DECIMAL(18,0) TRUE TRUE FALSE
D DECIMAL(20,5) TRUE FALSE TRUE
J DATE TRUE FALSE FALSE
K VARCHAR(5) UTF-8 TRUE FALSE FALSE